IF exists(Select 1 from sys.objects where [name] = 'sp_generacion' and [type] = 'P')
BEGIN
        drop procedure sp_generacion
END
GO
create procedure sp_generacion(
 @vcuenta nvarchar(510) = null,
 @vplan nvarchar(510) = null
 )
 as 
 begin


select 
pivots.PROGRAMA, pivots.CUENTA, pivots.CURSO, pivots.MATERIA, pivots.NOMBRE, pivots.NRC, pivots.DIA, pivots.HORARIO,
CAST(substring(pivots.HORARIO, 1, charindex('-',pivots.HORARIO)-1) AS TIME) AS HORAINICIO,
CAST(SUBSTRING(pivots.HORARIO,charindex('-',pivots.HORARIO)+1,LEN(pivots.HORARIO)) AS TIME) AS HORAFIN
FROM(
select
i.CODIGO_CAMPUS, i.PROGRAMA, i.CUENTA, i.materia,c.NOMBRE , i.valor, c.CONS, pa.NRC, pa.CURSO,
pa.LUNES, pa.MARTES, pa.MIERCOLES, pa.JUEVES, pa.VIERNES, pa.SABADO

from INTEGRADOTABLAS i
inner join dbo.COMPLETO c on i.PROGRAMA = c.[PLAN] and i.materia = c.BANNER
inner join dbo.PATA pa on pa.CLAVE = i.materia
where i.valor in ('P', '5', 'p')
and ( @vcuenta is null or i.CUENTA = @vcuenta) and (@vplan is null or @vplan = c.[PLAN]) 
group by 
 i.CODIGO_CAMPUS, i.PROGRAMA, i.CUENTA, i.materia, c.NOMBRE, i.valor, c.CONS, pa.NRC, pa.CURSO, pa.LUNES,
 pa.MARTES, pa.MIERCOLES, pa.JUEVES, pa.VIERNES, pa.SABADO) p
 UNPIVOT
 (HORARIO FOR DIA IN
	(LUNES, MARTES, MIERCOLES, JUEVES, VIERNES, SABADO)
 ) as pivots

 end
 go
 
 IF exists(Select 1 from sys.objects where [name] = 'sp_generacion' and [type] = 'P')
BEGIN
        PRINT 'SP Creado: sp_generacion '
END
GO
 